package com.example;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.HashMap;
import java.util.Map;

public class ExcelMerger {

    public static String path="C:\\Users\\Admin\\Desktop\\对重中心不动";
//    public static String path="C:\\Users\\Administrator\\Desktop\\excel合并\\对账";

    public static void main(String[] args) throws Exception {
        try {
            // 指定包含多个Excel文件的文件夹路径
            File folder = new File(path);
            File[] files = folder.listFiles((dir, name) -> name.endsWith(".xlsx"));

            // 创建一个新的工作簿用于合并
            Workbook mergedWorkbook = new XSSFWorkbook();

            // 用来存储三个Sheet的名字
            String[] sheetNames = {"交易收入", "消费者退款金额", "非商责平台售后补贴金额"};

            // 遍历文件夹中的所有Excel文件
            for (File file : files) {
                try (FileInputStream fis = new FileInputStream(file)) {
                    Workbook workbook = new XSSFWorkbook(fis);

                    // 样式缓存，避免重复创建
                    Map<CellStyle, CellStyle> styleCache = new HashMap<>();

                    // 合并每个Excel文件中的三个指定的Sheet
                    for (String sheetName : sheetNames) {
                        Sheet sourceSheet = workbook.getSheet(sheetName);
                        if (sourceSheet != null) {
                            Sheet targetSheet = getOrCreateSheet(mergedWorkbook, sheetName);

                            // 获取目标Sheet中的行数
                            int targetRowCount = targetSheet.getPhysicalNumberOfRows();

                            // 将源Sheet的数据复制到目标Sheet中
                            for (int i = sourceSheet.getFirstRowNum(); i <= sourceSheet.getLastRowNum(); i++) {
                                Row sourceRow = sourceSheet.getRow(i);
                                if (sourceRow != null) {
                                    Row targetRow = targetSheet.createRow(targetRowCount++);
                                    copyRow(sourceRow, targetRow, mergedWorkbook, styleCache);
                                }
                            }
                        }
                    }
                }
            }

            // 写入到新的Excel文件
            try (FileOutputStream fos = new FileOutputStream(path+"\\merged_excel.xlsx")) {
                mergedWorkbook.write(fos);
            }

            System.out.println("Excel files merged successfully!");
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 暂停
        System.in.read();
    }

    // 获取或创建一个新的Sheet
    private static Sheet getOrCreateSheet(Workbook workbook, String sheetName) {
        Sheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            sheet = workbook.createSheet(sheetName);
        }
        return sheet;
    }

    // 复制行内容
    private static void copyRow(Row sourceRow, Row targetRow, Workbook targetWorkbook, Map<CellStyle, CellStyle> styleCache) {
        for (int i = sourceRow.getFirstCellNum(); i < sourceRow.getPhysicalNumberOfCells(); i++) {
            Cell sourceCell = sourceRow.getCell(i);
            if (sourceCell != null) {
                Cell targetCell = targetRow.createCell(i);
                copyCell(sourceCell, targetCell, targetWorkbook, styleCache);
            }
        }
    }

    // 复制单元格内容，优化样式复制
    private static void copyCell(Cell sourceCell, Cell targetCell, Workbook targetWorkbook, Map<CellStyle, CellStyle> styleCache) {
        // 复用已存在的样式，避免重复创建
        CellStyle sourceStyle = sourceCell.getCellStyle();
        CellStyle targetStyle = styleCache.computeIfAbsent(sourceStyle, s -> {
            CellStyle newStyle = targetWorkbook.createCellStyle();
            newStyle.cloneStyleFrom(s);
            return newStyle;
        });

        targetCell.setCellStyle(targetStyle);

        // 复制单元格的内容
        switch (sourceCell.getCellType()) {
            case STRING:
                targetCell.setCellValue(sourceCell.getStringCellValue());
                break;
            case NUMERIC:
                targetCell.setCellValue(sourceCell.getNumericCellValue());
                break;
            case BOOLEAN:
                targetCell.setCellValue(sourceCell.getBooleanCellValue());
                break;
            case FORMULA:
                targetCell.setCellFormula(sourceCell.getCellFormula());
                break;
            default:
                targetCell.setCellValue("");
        }
    }
}
